



alter   procedure p_vounoreset (
  @fyear int,
  @fmonth int,
  @vouwordid int,
  @binder tinyint,
  @ordertype tinyint,
  @resetcount integer out
) as  

  declare @vouyearmonth varchar(6);
  declare @voucherid integer;
  declare @i integer;
  declare @local_tab table(voucherid integer)

begin
  set nocount on
  set @resetcount = 0;
  set @vouyearmonth=cast(@fyear as char(4))+ right(cast(100+@fmonth as char(3)),2)
  set @i = 1;
  if (@binder = 1)
  begin
    if @ordertype=0
      insert into @local_tab    
      select voucherid from tfb_voucher 
        where vouwordid = @vouwordid and vouyearmonth = @vouyearmonth
        order by bdate, voucherid
    else if @ordertype=1        
      insert into @local_tab    
      select voucherid from tfb_voucher 
        where vouwordid = @vouwordid and vouyearmonth = @vouyearmonth
        order by voucherno
    declare cur_vounoreset cursor for select * from @local_tab 
    open cur_vounoreset 
    fetch next from cur_vounoreset into @voucherid 
    while @@fetch_status = 0 
    begin
      update tfb_voucher set voucherno = @i where voucherid = @voucherid;
      set @i = @i + 1;
      set @resetcount = @resetcount + 1;
      fetch next from cur_vounoreset into @voucherid 
    end
    close cur_vounoreset
    deallocate cur_vounoreset
  end
  else 
  begin
    select @i=max(voucherno) from tfb_voucher where vouwordid = @vouwordid
      and vouyearmonth = @vouyearmonth and (binder_by is not null) ;
    set @i = isnull(@i,0) + 1;
    if @ordertype=0
      insert into @local_tab    
      select voucherid from tfb_voucher where vouwordid = @vouwordid
        and vouyearmonth = @vouyearmonth and (binder_by is null)
        order by bdate, voucherid
    else if @ordertype=1
      insert into @local_tab    
      select voucherid from tfb_voucher where vouwordid = @vouwordid
        and vouyearmonth = @vouyearmonth and (binder_by is null)
        order by voucherno
    declare cur_vounoreset cursor for select * from @local_tab 
    open  cur_vounoreset 
    fetch next from  cur_vounoreset into @voucherid
    while @@fetch_status = 0 
    begin
      update tfb_voucher set voucherno = @i where voucherid = @voucherid;
      set @i = @i + 1;
      set @resetcount = @resetcount + 1;
      fetch next from cur_vounoreset into @voucherid
    end
    close cur_vounoreset
    deallocate cur_vounoreset
  end
end



GO
